from sqlalchemy import ForeignKey, Column, Integer, String, Text , create_engine 
from sqlalchemy.orm import sessionmaker, relation, backref
from pprint import  pprint


engine = create_engine('sqlite:///colour.db', echo=True)
from sqlalchemy.ext.declarative import declarative_base 
Base = declarative_base()

Session = sessionmaker(bind=engine)
s = Session()

class Mine():
    def save(self):
        s.add(self)   
        
    def read(self):
        #return s.query(self.__class__).filter('colours.name="Pink"').one()
        return s.query(self.__class__).filter('colours.name="{0}"'.format(self.name)).one()
    
    def all(self):
        return s.query(self.__class__).all()
    
    def delete(self):
        s.delete(self)
    

class Colour(Base,Mine):
    __module__ = __name__
    __tablename__ = 'colours'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', Text, unique = True, nullable=False)
    
    def __init__(self, name=None):
        self.name = name

    def __repr__(self):
        return "<Colour %s %s>" % (self.id, self.name)
    
#    def save(self):
#        s.add(self)   
#        
#    def read(self):
#        return s.query(self.__class__).filter('colours.name="Pink"').one()
#        return s.query(self.__class__).filter('colours.name="{0}"'.format(self.name)).one()
#    
#    def all(self):
#        return s.query(self.__class__).all()
#    
#    def delete(self):
#        s.delete(self)

        

class User(Base):
    __module__ = __name__
    __tablename__ = 'users'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', Text, unique = True) 
    colour_id = Column(Integer, ForeignKey('colours.id'))
    colour = relation(Colour, order_by='Colour.id', backref="user")
    

    def __init__(self):
        self.name = name

    def __repr__(self):
        return "<User %s %s>" % (self.id, self.name)

# colours_table = Colour.__table__ # needed?
metadata = Base.metadata

def session_open():
    Session = sessionmaker(bind=engine)
    return Session()

if __name__ == '__main__':
 
###  CREATE TABLES / SESSION
    metadata.create_all(engine)
#    s = session_open()
    
### Create some users
#    s.add_all([User('Andy'),User('Bertie'),User('Carol')])
#    s.commit()
#    for u in s.query(User): print u, u.colour
#    print 10, '==================================================='
# 
   
### Create some colours   
#    s.add_all([Colour('Apple'),Colour('Brown'),Colour('Cream')])
#    s.commit()
#    for c in s.query(Colour): print c
#    print 20, '==================================================='
#

### Assign colours
#    u1 = s.query(User).filter("users.name = 'Andy'").one()
#    u2 = s.query(User).filter("users.name = 'Bertie'").one()
#    u3 = s.query(User).filter("users.name = 'Carol'").one()
#    print 30, '==================================================='
#    u1.colour = s.query(Colour).filter("colours.name = 'Apple'").one()
#    u1.colour = s.query(Colour).filter("colours.name = 'Brown'").one()
#    u1.colour = s.query(Colour).filter("colours.name = 'Cream'").one()
#    print 40, '==================================================='
#    s.commit()
#    print 50, '==================================================='

### Add existing colour to existing user
#    c = s.query(Colour).filter('name="Apple"').one()
#    u = s.query(User).filter('name="Andy"').one() 
#    u.favourite_colour = c
#    print 10, u.favourite_colour
#    print 20, u.favourite_colour.user
#    print 30
#    s.commit()

#### Add new colour to new user
#    c = Colour('Green')
#    u = User('George')
#    u.colour = c
#    print 10, u.colour
#    s.add(u)
#    s.commit()
#    print 20, u.colour.user
#    print 30
#    # s.commit()

### Fix ellie colour
#    u = s.query(User).filter("users.name = 'Ellie'").one()
#    print 5, u, u.colour
#    u.colour = Colour('Extreme')
#    print 10, u.colour.id
#    s.add(u)
#    s.commit()
#    print 20, u, u.colour
#    print 30, u.colour.id
#    c = s.query(Colour).filter("colours.name = 'Extreme'").one()
#    print 40, c, c.id

### accessing records
#    # pprint(dir(s.query(User)))
#    print 10, s.query(User).all()  # list
#    print 20, s.query(User).count() # int
#    print 20, s.query(User).distinct() # sql query string
#    print 40, s.query(User).get(1) # user
#    print 50, s.query(User).instances(1) # ? generator object

### Try short method to add record
#    print 10 * '===='
#    print 10,'====', Colour("Purple1").save()
#    print 15,'====', Colour("Purple2").save()
#    print 20,'====', Colour("Purple3").save()
#    s.commit()
#    print 30,'====', Colour('Orange1').read()
#    print 35, Colour('Orange2').read()
#    print 40, Colour('Orange3').read()
#    # compare sql output with normal method
#    print 10 * '===='
#    c1 = Colour('Sand1')
#    c2 = Colour('Sand2')
#    c3 = Colour('Sand3')
#    s.add_all([c1,c2,c3])
#    s.commit()
#    print 42,c1.id
#    print 44, c2.id
#    print 45, c3.id
#    print 50, s.query(Colour).filter('colours.name="Sand1"')
#    print 60, s.query(Colour).filter('colours.name="Sand2"')
#    print 60, s.query(Colour).filter('colours.name="Sand3"')

#### Delete
#    c = Colour('Orange1').read()
#    print 10, c, c.id
#    s.delete(c)
#    print 20
#    c = Colour('Orange1').read()
#    print 10, c, c.id

## Select all
#    print 10, s.query(Colour).all()
#    print 20, Colour().all()
    
## Summary
    unique = 'Blue11'
    unique2 = 'Blue12'
    print 10, Colour(unique).save()
    s.commit()
    c = Colour(unique).read()
    print 15, c
    c.name = unique2
    s.commit()
    c = Colour(unique2).read()
    print 20, c
    c.delete()
    # print Colour(unique2).read()
    print 30
    
    
    
    
